package com.hejia.alauda.persistence.mybatis;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.ResultMap;
import org.apache.ibatis.annotations.ResultType;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.hejia.alauda.entity.Financing;
import com.hejia.alauda.entity.ProductCash;
import com.hejia.alauda.entity.SettlementCount;
import com.hejia.alauda.entity.User;
import com.hejia.alauda.persistence.BaseMapper;
import com.hejia.alauda.utils.Pager;

public interface FinancingMapper extends BaseMapper<Financing> {

	/**
	 * 开始计息
	 * 
	 * @param params
	 * @return
	 */
	@Update("UPDATE `financing` SET `status`=#{status},`modifyDate`=now() WHERE `status`=\'STATUS_DEFAULT\' AND DATE_FORMAT(`valueDate`, \'%Y-%m-%d\')<=#{date}")
	int startCalculateInterest(Map<String, Object> params);

	/**
	 * 结束计息
	 * 
	 * @param params
	 * @return
	 */
	@Update("UPDATE `financing` SET `status`=#{status},`modifyDate`=now() WHERE `status`=\'STATUS_INTEREST\' AND DATE_FORMAT(`settlementDate`, \'%Y-%m-%d\')<=#{date}")
	int finishCalculateInterest(Map<String, Object> params);

	/**
	 * 根据银行卡号统计该银行卡下未回款资产包数
	 * 
	 * @param bankCardId
	 * @return
	 */
	@Select("select count(1) from  `financing` a,`order` b WHERE a.order_id=b.id and  a.status!='STATUS_REPAID' AND b.bankcard_id=#{bankCardId}")
	int countUnrepaidFinancing(Long bankCardId);

	/**
	 * 根据银行卡号获取该银行卡下未回款资产包数
	 * 
	 * @param bankCardId
	 * @return
	 */
	@Select("select a.* from  `financing` a,`order` b WHERE a.order_id=b.id and  a.`status`!='STATUS_REPAID' AND b.bankcard_id=#{bankCardId}")
	@ResultMap("ListMap")
	List<Financing> findUnrepaidFinancing(Long bankCardId);

	@Select("SELECT f.`id` FROM `financing` f WHERE f.id NOT IN(SELECT a.`id` FROM `financing` a JOIN `interest` b ON a.`id`=b.`financing_id`)")
	@ResultType(value = Map.class)
	List<Map<String, Object>> findNonInterestFinancing();

	@Select("SELECT `id`,`amount`,`interestRate`,TO_DAYS(now())-TO_DAYS(`valueDate`) AS `term`,`valueDate` FROM `financing` WHERE DATE_FORMAT(`settlementDate`, \'%Y-%m-%d\')>=#{settlementDate}")
	@ResultType(value = Financing.class)
	List<Financing> findNonExpiredFinancing(Map<String, Object> params);

	@Update("UPDATE `financing`, (SELECT DISTINCT `interest`.`income`, `interest`.`financing_id`, `interest`.`valueDate` FROM `interest` WHERE DATE_FORMAT(`interest`.`valueDate`, '%Y-%m-%d') = #{date}) i SET `financing`.`income` = i.`income` WHERE `financing`.`id` = i.`financing_id`")
	int updateFinancingIncomeById(Map<String, Object> params);

	List<Map<String, Object>> findByUserId(Map<String, Object> params);

	Integer findCountByUserId(Map<String, Object> params);

	@Select("select sum(`amount`) from `financing` where user_id=#{user_id} and (status<>\'STATUS_PAYBACK\' and status<>\'STATUS_REPAID\')")
	Double findTotalAmountByUserId(Long user_id);

	BigDecimal findTotalAmountByProperty(Map<String, Object> params);

	List<Map<String, Object>> findInvestment(
			@Param("params") Map<String, Object> params,
			@Param("pager") Pager pager);

	Integer findInvestmentCount(@Param("params") Map<String, Object> params);

	/*
	 * 计算用户的累计收益
	 */
	@Select("select max(b.`income`) as `income` from `interest` b where DATE_FORMAT(b.`valueDate`, \'%Y-%m-%d\')<DATE_FORMAT(now(), \'%Y-%m-%d\') AND `financing_id`=#{financing_id}")
	@ResultType(value = BigDecimal.class)
	BigDecimal findIncomeGroupByFinancing(Long financing_id);

	@Update("update `financing` set status=#{0},modifyDate=now(),repaymentTime=now() where order_id=#{1}")
	boolean setFinalStatusByOrderId(Financing.Status status, Long orderId);

	/**
	 * 统计日金融包信息(总金额，总笔数，未申请打款金额，未申请打款笔数
	 * 
	 * @param date
	 * @return
	 */
	@Select("select sum(amount) as `totalAmount`,count(1) as `totalCount`,sum(IF(`status`!='STATUS_PAYAPPLY' and `status`!='STATUS_PAYBACK' and `status`!='STATUS_REPAID',amount,0)) as unapplyAmount ,sum(IF(`status`!='STATUS_PAYAPPLY' and `status`!='STATUS_PAYBACK' and `status`!='STATUS_REPAID',1,0)) as unapplyCount from `financing` where DATE_FORMAT(DATE_ADD(valueDate, INTERVAL term DAY),'%Y-%m-%d')=#{date}")
	@ResultType(value = Map.class)
	List<Map<String, Object>> statDayFinancing(String date);

	/**
	 * 获取到期的金融包列表
	 * 
	 * @param params
	 * @return
	 */
	List<Map<String, Object>> listExpiredFinancing(Map<String, Object> params);

	/**
	 * 统计到期的金融包条数
	 * 
	 * @param params
	 * @return
	 */
	Integer countExpiredFinancing(Map<String, Object> params);

	/**
	 * 根据地区分析保有量
	 * 
	 * @return
	 */
	public List<Map<String, Object>> statInventoryByRegion();

	/**
	 * 根据地区分析保有量(浙江省内)
	 * 
	 * @return
	 */
	public List<Map<String, Object>> statInventoryInZheJiang();

	/**
	 * 根据类别分析保有量
	 * 
	 * @return
	 */
	public List<Map<String, Object>> statInventoryByType();

	/**
	 * 根据地区查保有量，前十条
	 * 
	 * @return
	 */
	public List<Map<String, Object>> statByRegionLimit();

	/**
	 * 根据浙江省内查保有量，前十条
	 * 
	 * @return
	 */
	public List<Map<String, Object>> statInZheJiangLimit();

	/**
	 * 分钟销售量
	 * 
	 * @param statDate
	 *            统计日期
	 * @return
	 */
	@Select("select hour(createDate)*60+minute(createDate) as minute,sum(amount) as sales from financing where DATE_FORMAT(createDate,'%Y-%m-%d')=#{0}  group by hour(createDate)*60+minute(createDate) order by minute ")
	@ResultType(value = Map.class)
	public List<Map<String, Object>> minuteSales(String statDate);

	/**
	 * 日销售量
	 * 
	 * @param startDate
	 *            开始日期
	 * @param endDate
	 *            结束日期
	 * @return
	 */
	@Select("select DATE_FORMAT(createDate,'%Y-%m-%d') as day,sum(amount) as amount from financing where DATE_FORMAT(createDate,'%Y-%m-%d')>=#{0} and DATE_FORMAT(createDate,'%Y-%m-%d')<=#{1} group by DATE_FORMAT(createDate,'%Y-%m-%d') order by day")
	@ResultType(value = Map.class)
	public List<Map<String, Object>> daySales(String startDate, String endDate);

	/**
	 * 查询支出
	 * 
	 * @return
	 */
	public Double selectExpend();

	/**
	 * 根据月份查询保有量周期组成
	 * 
	 * @param startdate
	 * @param enddate
	 * @return
	 */
	public List<Map<String, Object>> statInventoryByMonth(String startdate,
			String enddate);

	/**
	 * 根据周查询保有量周期组成
	 * 
	 * @param startdate
	 * @param enddate
	 * @return
	 */
	public List<Map<String, Object>> statInventoryByWeek(String startdate,
			String enddate);

	/**
	 * 根据年查询保有量周期组成
	 * 
	 * @param startdate
	 * @param enddate
	 * @return
	 */
	public List<Map<String, Object>> statInventoryByYear(String startdate,
			String enddate);

	/**
	 * 根据周查询保有量收益率组成
	 * 
	 * @param startdate
	 * @param enddate
	 * @return
	 */
	public List<Map<String, Object>> statByIntereByWeek(String startdate,
			String enddate);

	/**
	 * 根据月查询保有量收益率组成
	 * 
	 * @param startdate
	 * @param enddate
	 * @return
	 */
	public List<Map<String, Object>> statByIntereByMonth(String startdate,
			String enddate);

	/**
	 * 根据年查询保有量收益率组成
	 * 
	 * @param startdate
	 * @param enddate
	 * @return
	 */
	public List<Map<String, Object>> statByIntereByYear(String startdate,
			String enddate);

	/**
	 * 初始化保有量趋势
	 * 
	 * @return
	 */
	public Map<String, Object> stattrendByMonth(String enddate);

	/**
	 * 
	 * @param date
	 * @return
	 */
	public List<Financing> dailyCheckList(Financing financing);

	/**
	 * 销售量现状-根据地区
	 * 
	 * @return
	 */
	public List<Map<String, Object>> salesByRegion();

	/**
	 * 销售量现状-根据年化率
	 * 
	 * @return
	 */
	public List<Map<String, Object>> salesByRate();

	/**
	 * 销售量组成-根据月份
	 * 
	 * @return
	 */
	public List<Map<String, Object>> salesByMonth(String startdate,
			String enddate);

	/**
	 * 销售量组成-根据周
	 * 
	 * @return
	 */
	public List<Map<String, Object>> salesByWeek(String startdate,
			String enddate);

	/**
	 * 销售量组成-根据年
	 * 
	 * @return
	 */
	public List<Map<String, Object>> salesByYear(String startdate,
			String enddate);

	/**
	 * 根据日期统计结息金额
	 * 
	 * @param map
	 * @return
	 */
	List<SettlementCount> countSettlementAmountByDate(Map<String, Object> map);

	List<Financing> productName(Map<String, Object> map);

	/**
	 * 查询当天finacing兑付量
	 * 
	 * @param statdate
	 * @return
	 */
	@Select("select sum(amount) from `financing` where str_to_date(repaymentTime,'%Y-%m-%d')=#{0} and (`status`='STATUS_REPAID' or `status`='STATUS_PAYBACK')")
	@ResultType(value = Double.class)
	public Double selectPaymentByDay(String statdate);

	/**
	 * 赎回人数
	 * 
	 * @param startdate
	 * @param enddate
	 * @return
	 */
	@Select("select count(DISTINCT(user_id)) from financing a where (a.`status`='STATUS_REPAID' or a.`status`='STATUS_PAYBACK') and str_to_date(repaymentTime,'%Y-%m-%d')>=#{1} and #{0}>=str_to_date(repaymentTime,'%Y-%m-%d')")
	@ResultType(value = Integer.class)
	public Integer paymentPersons(String startdate, String enddate);

	/**
	 * 所有赎回人数
	 * 
	 * @param startdate
	 * @param enddate
	 * @return
	 */
	@Select("select count(DISTINCT(user_id)) from financing a where a.`status`='STATUS_REPAID' or a.`status`='STATUS_PAYBACK'")
	@ResultType(value = Integer.class)
	public Integer allpaymentPersons();

	/**
	 * 查询指定时间支付网关下的银行的分别交易金额
	 * 
	 * @param
	 * @return
	 */
	@Select("select ifnull(a.`payGateCode`,0) as pgc,c.`bankName` as bn,c.`bankCode` as bc,sum(a.`amount`) as num from `financing` a,`order` b,`bankcard` c where a.`order_id`=b.`id` and b.`bankcard_id`=c.`id` and str_to_date(a.`createDate`,'%Y-%m-%d')>=#{0} and str_to_date(a.`createDate`,'%Y-%m-%d')<=#{1} group by a.`payGateCode`,c.`bankCode` order by pgc")
	@ResultType(value = Map.class)
	public List<Map<String, Object>> paygatebankStat(String startdate,
			String enddate);

	/**
	 * 查询所有支付网关下的银行的分别交易金额
	 * 
	 * @param
	 * @return
	 */
	@Select("select ifnull(a.`payGateCode`,0) as pgc,c.`bankName` as bn,c.`bankCode` as bc,sum(a.`amount`) as num from `financing` a,`order` b,`bankcard` c where a.`order_id`=b.`id` and b.`bankcard_id`=c.`id` group by a.`payGateCode`,c.`bankCode` order by pgc")
	@ResultType(value = Map.class)
	public List<Map<String, Object>> allPaygatebankStat();

	/**
	 * 根据产品id查询销量排名前10的资产列表
	 * 
	 * @param product_id
	 * @return
	 */
	@Select("SELECT user_id,sum(amount) AS amount,max(createDate) AS createDate FROM financing WHERE product_id=#{product_id} GROUP BY user_id ORDER BY amount DESC,createDate")
	@ResultMap("ListMap")
	public List<Financing> findFinancingTop10ByProductId(Long product_id);

	/**
	 *
	 * @param financing
	 * @return
	 */
	List<Financing> allFinancingWithDateGroupByName(Financing financing);

	/**
	 * 给2015年三月送流量的活动查询是否到一万元的资格（除新手产品）
	 * 
	 * @param params
	 * @return
	 */
	BigDecimal findTotalAmountForMarchTraffic2015(Map<String, Object> params);

	/**
	 * 查询符合2015年三月送流量的活动资格的用户名单
	 * 
	 * @return
	 */
	List<Long> monitorTotalAmountForMarchTraffic2015(Map<String, Object> params);

	/**
	 * 给2015年四月送流量的活动查询是否到一万元的资格（除新手产品）
	 * 
	 * @param params
	 * @return
	 */
	BigDecimal findTotalAmountForAprilTraffic2015(Map<String, Object> params);

	/**
	 * 查询用户是查询条件下的总消费金额（不包含新手产品）
	 * 
	 * @param start
	 *            活动开始时间
	 * @param end
	 *            活动结束时间
	 * @param id
	 *            用户ID
	 * 
	 * @return BigDecimal
	 * */
	BigDecimal findActivityIntervelAmounts(Map<String, Object> params);

	/**
	 * 查询用户是查询条件下的单笔消费金额（不包含新手产品）
	 * 
	 * @param start
	 *            活动开始时间
	 * @param end
	 *            活动结束时间
	 * @param id
	 *            用户ID
	 * 
	 * @return int 满足条件的数量笔数
	 * */
	int findSingleAmount(Map<String, Object> params);

	/**
	 * 查询用户是查询条件下的单笔消费金额（不包含新手产品）
	 * 
	 * @param start
	 *            活动开始时间
	 * @param end
	 *            活动结束时间
	 * @param id
	 *            用户ID
	 * 
	 * @return int 满足条件的数量笔数
	 * */
	int findSingleAmountBy51(Map<String, Object> params);

	Map<String, Long> findMinAndMaxId();

	List<Financing> findFinancingForCalculateByProperty(
			@Param("params") Map<String, Object> params,
			@Param("pager") Pager<Financing> pager);

	/**
	 * 支付网关统计——最大交易金额（无时间条件）
	 * 
	 * @return
	 */
	@Select("select ifnull(x.pgc,0) as pgc,x.bn,x.bc,x.num from (select a.`payGateCode` as pgc,c.`bankName` as bn,c.`bankCode` as bc,a.`amount` as num from `financing` a,`order` b,`bankcard` c where a.`order_id`=b.`id` and b.`bankcard_id`=c.`id` order by a.`amount` desc)x group by x.pgc,x.bc")
	@ResultType(value = Map.class)
	public List<Map<String, Object>> allPaygateHighAmount();

	/**
	 * 支付网关统计——最大交易金额（有时间条件）
	 * 
	 * @return
	 */
	@Select("select ifnull(x.pgc,0) as pgc,x.bn,x.bc,x.num from (select a.`payGateCode` as pgc,c.`bankName` as bn,c.`bankCode` as bc,a.`amount` as num from `financing` a,`order` b,`bankcard` c where a.`order_id`=b.`id` and b.`bankcard_id`=c.`id` and str_to_date(a.`createDate`,'%Y-%m-%d')>=#{0} and str_to_date(a.`createDate`,'%Y-%m-%d')<=#{1} order by a.`amount` desc)x group by x.pgc,x.bc")
	@ResultType(value = Map.class)
	public List<Map<String, Object>> paygateHighAmount(String startdate,
			String enddate);

	/**
	 * 支付网关统计——交易笔数（无时间条件）
	 * 
	 * @return
	 */
	@Select("select ifnull(b.`payGateCode`,0) as pgc,c.`bankName` as bn,c.`bankCode` as bc,count(1) as num from `order` b,`bankcard` c where b.`bankcard_id`=c.`id` group by b.`payGateCode`,c.`bankCode` order by payGateCode")
	@ResultType(value = Map.class)
	public List<Map<String, Object>> allPaygateOrder();

	/**
	 * 支付网关统计——交易笔数（有时间条件）
	 * 
	 * @return
	 */
	@Select("select ifnull(b.`payGateCode`,0) as pgc,c.`bankName` as bn,c.`bankCode` as bc,count(1) as num from `order` b,`bankcard` c where b.`bankcard_id`=c.`id` and str_to_date(b.`createDate`,'%Y-%m-%d')>=#{0} and str_to_date(b.`createDate`,'%Y-%m-%d')<=#{1} group by b.`payGateCode`,c.`bankCode` order by payGateCode")
	@ResultType(value = Map.class)
	public List<Map<String, Object>> paygateOrder(String startdate,
			String enddate);

	/**
	 * 支付网关统计——交易成功笔数（无时间条件）
	 * 
	 * @return
	 */
	@Select("select ifnull(b.`payGateCode`,0) as pgc,c.`bankName` as bn,c.`bankCode` as bc,count(1) as num from `order` b,`bankcard` c where b.`bankcard_id`=c.`id` and b.`status`='STATUS_PAID' group by b.`payGateCode`,c.`bankCode` order by payGateCode")
	@ResultType(value = Map.class)
	public List<Map<String, Object>> allPaygateSuccessOrder();

	/**
	 * 支付网关统计——交易成功笔数（有时间条件）
	 * 
	 * @return
	 */
	@Select("select ifnull(b.`payGateCode`,0) as pgc,c.`bankName` as bn,c.`bankCode` as bc,count(1) as num from `order` b,`bankcard` c where b.`bankcard_id`=c.`id` and b.`status`='STATUS_PAID' and str_to_date(b.`createDate`,'%Y-%m-%d')>=#{0} and str_to_date(b.`createDate`,'%Y-%m-%d')<=#{1} group by b.`payGateCode`,c.`bankCode` order by payGateCode")
	@ResultType(value = Map.class)
	public List<Map<String, Object>> paygateSuccessOrder(String startdate,
			String enddate);

	/**
	 * 支付网关统计——汇总交易总金额（无时间条件）
	 * 
	 * @param startdate
	 * @param enddate
	 * @return
	 */
	@Select("select ifnull(a.`payGateCode`,0) as pgc,sum(a.`amount`) as num from `financing` a,`order` b,`bankcard` c where a.`order_id`=b.`id` and b.`bankcard_id`=c.`id` group by a.`payGateCode` order by pgc")
	@ResultType(value = Map.class)
	public List<Map<String, Object>> gatherAllPayGateAmount();

	/**
	 * 支付网关统计——汇总交易总金额（有时间条件）
	 * 
	 * @param startdate
	 * @param enddate
	 * @return
	 */
	@Select("select ifnull(a.`payGateCode`,0) as pgc,sum(a.`amount`) as num from `financing` a,`order` b,`bankcard` c where a.`order_id`=b.`id` and b.`bankcard_id`=c.`id` and str_to_date(a.`createDate`,'%Y-%m-%d')>=#{0} and str_to_date(a.`createDate`,'%Y-%m-%d')<=#{1} group by a.`payGateCode` order by pgc")
	@ResultType(value = Map.class)
	public List<Map<String, Object>> gatherPayGateAmount(String startdate,
			String enddate);

	/**
	 * 支付网关统计——汇总交易最大金额（无时间条件）
	 * 
	 * @param startdate
	 * @param enddate
	 * @return
	 */
	@Select("select ifnull(x.pgc,0) as pgc,x.num from (select a.`payGateCode` as pgc,a.`amount` as num from `financing` a,`order` b,`bankcard` c where a.`order_id`=b.`id` and b.`bankcard_id`=c.`id` order by a.`amount` desc)x group by x.pgc")
	@ResultType(value = Map.class)
	public List<Map<String, Object>> gatherAllPayGateHighAmount();

	/**
	 * 支付网关统计——汇总交易最大金额（有时间条件）
	 * 
	 * @param startdate
	 * @param enddate
	 * @return
	 */
	@Select("select ifnull(x.pgc,0) as pgc,x.num from (select a.`payGateCode` as pgc,a.`amount` as num from `financing` a,`order` b,`bankcard` c where a.`order_id`=b.`id` and b.`bankcard_id`=c.`id` and str_to_date(a.`createDate`,'%Y-%m-%d')>=#{0} and str_to_date(a.`createDate`,'%Y-%m-%d')<=#{1} order by a.`amount` desc)x group by x.pgc")
	@ResultType(value = Map.class)
	public List<Map<String, Object>> gatherPayGateHighAmount(String startdate,
			String enddate);

	/**
	 * 支付网关统计——汇总交易笔数（无时间条件）
	 * 
	 * @param startdate
	 * @param enddate
	 * @return
	 */
	@Select("select ifnull(b.`payGateCode`,0) as pgc,count(1) as num from `order` b,`bankcard` c where b.`bankcard_id`=c.`id` group by b.`payGateCode` order by payGateCode")
	@ResultType(value = Map.class)
	public List<Map<String, Object>> gatherAllPayGateOrder();

	/**
	 * 支付网关统计——汇总交易笔数（有时间条件）
	 * 
	 * @param startdate
	 * @param enddate
	 * @return
	 */
	@Select("select ifnull(b.`payGateCode`,0) as pgc,count(1) as num from `order` b,`bankcard` c where b.`bankcard_id`=c.`id` and str_to_date(b.`createDate`,'%Y-%m-%d')>=#{0} and str_to_date(b.`createDate`,'%Y-%m-%d')<=#{1} group by b.`payGateCode` order by payGateCode")
	@ResultType(value = Map.class)
	public List<Map<String, Object>> gatherPayGateOrder(String startdate,
			String enddate);

	/**
	 * 支付网关统计——汇总成功交易笔数（无时间条件）
	 * 
	 * @param startdate
	 * @param enddate
	 * @return
	 */
	@Select("select ifnull(b.`payGateCode`,0) as pgc,count(1) as num from `order` b,`bankcard` c where b.`bankcard_id`=c.`id` and b.`status`='STATUS_PAID' group by b.`payGateCode` order by payGateCode")
	@ResultType(value = Map.class)
	public List<Map<String, Object>> gatherAllPayGateSuccessOrder();

	/**
	 * 支付网关统计——汇总成功交易笔数（有时间条件）
	 * 
	 * @param startdate
	 * @param enddate
	 * @return
	 */
	@Select("select ifnull(b.`payGateCode`,0) as pgc,count(1) as num from `order` b,`bankcard` c where b.`bankcard_id`=c.`id` and b.`status`='STATUS_PAID' and str_to_date(b.`createDate`,'%Y-%m-%d')>=#{0} and str_to_date(b.`createDate`,'%Y-%m-%d')<=#{1} group by b.`payGateCode` order by payGateCode")
	@ResultType(value = Map.class)
	public List<Map<String, Object>> gatherPayGateSuccessOrder(
			String startdate, String enddate);

	@Select("SELECT TO_DAYS(#{date2})-TO_DAYS(#{date1}) FROM dual")
	@ResultType(value = Integer.class)
	public int findDatesInterval(Map<String, Object> params);

	List<Financing> findFinancingRankByProperty(Map<String, Object> params);

	List<Financing> findUserFinancingRankByProperty(Map<String, Object> params);

	List<Map<String, Object>> findTestFinancingByProperty(
			Map<String, Object> params);

	@Select("SELECT SUM(amount) FROM financing WHERE product_id IN(SELECT id FROM product WHERE rookie=false) AND user_id=#{user_id}")
	BigDecimal findUserTotalAmountExceptRookie(Long id);

	@Select("SELECT SUM(IFNULL(income, 0)) AS income, SUM(CASE WHEN `status` = 'STATUS_PAYBACK' THEN 0 WHEN `status` = 'STATUS_REPAID' THEN 0 ELSE amount END) AS assets, SUM(CASE WHEN `status` = 'STATUS_PAYBACK' THEN 0 WHEN `status` = 'STATUS_REPAID' THEN 0 ELSE IFNULL(income, 0) END) AS interest FROM financing WHERE user_id = #{user_id} AND id<>393772")
	Map<String, Object> findAssetsByUser(Long id);

	/**
	 * 统计未兑付的产品
	 * 
	 * @return
	 */
	@Select("select p.id productId, p.name productName, p.totalAmount totalAmount, sum(f.amount) soldAmount, p.startTime startTime, p.endTime endTime,"
			+ " date(f.valueDate) saleDate, o.payGateCode, date(f.settlementDate) expectRepaymentDate, p.term"
			+ " from financing f join product p on f.product_id = p.id"
			+ " join `order` o on f.order_id = o.id"
			+ " where f.valueDate < date(now()) and f.settlementDate >= date(now())"
			+ " group by p.id, date(f.valueDate), o.payGateCode;")
	List<ProductCash> countNotCashProduct();

	@Select("SELECT COUNT(DISTINCT user_id) FROM financing WHERE DATE(createDate)>=\'2015-07-01\'")
	@ResultType(value = Integer.class)
	int findPeopleNumberAfterJune();

	List<Financing> findFinancingByKeyword(
			@Param("startNumber") Integer startNumber,
			@Param("endNumber") Integer endNumber,
			@Param("keyword") String keyword);

	Integer countFinancingByKeyword(@Param("keyword") String keyword);

	List<Financing> findApplyFinancingByKeywordAndDate(
			@Param("startNumber") Integer startNumber,
			@Param("endNumber") Integer endNumber,
			@Param("keyword") String keyword,
			@Param("settlementDate") Date settlementDate);

	Integer countApplyFinancingByKeywordAndDate(
			@Param("keyword") String keyword,
			@Param("settlementDate") Date settlementDate);

	@Select("SELECT user_id,sum(amount)AS amount,min(DATE(settlementDate))AS min_date,max(DATE(settlementDate))AS max_date FROM financing GROUP BY user_id HAVING amount >= #{amount} AND((max_date < #{date2} AND max_date > #{date3}) OR min_date > #{date1})")
	@ResultType(value = Map.class)
	List<Map<String, Object>> findBondQualification(Map<String, Object> params);

	@Select("select sum(b.`amount`) bonus from `financing` f,`bonusorder` b where f.`order_id` = b.`order_id` and f.`order_id` = #{0}")
	BigDecimal findBonusByOrderId(Long id);

	/**
	 * 查询沉睡用户
	 * 
	 * @param map
	 * @return
	 */
	List<Financing> selectSleepingUsers(Map<String, Object> map);

	/**
	 * 查询沉睡用户条数
	 * 
	 * @param map
	 * @return
	 */
	int selectSleepingUsersCount(Map<String, Object> map);
	
	
	BigDecimal getTatalIncomeForUer();
	
	@Select("select sum(amount) from `financing` where status='STATUS_REPAID'")
	BigDecimal getFinishedAmount();
	
	/**
	 * 查询历史最高资产及收益
	 * @param user
	 * @return
	 */
	@Select("SELECT SUM(f.amount) amount ,SUM(f.income) income from financing f "
			+ "LEFT JOIN interest i ON  f.id = i.financing_id "
			+ "WHERE f.user_id = #{user_id} "
			+ "GROUP BY i.valueDate "
			+ "ORDER BY amount desc "
			+ "LIMIT 0 , 1 ")
	Map<String, Object> selectPastMaxAssetByUser(@Param("user_id") Long userId);
}
